-- @owner: @zhanghuan96
-- @date: 2024/12/16
-- @testpoint: gms_utility.exec_ddl_statement，语句为创建、修改、数据类型转换
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_utility cascade;
create extension gms_utility;
set behavior_compat_options='bind_procedure_searchpath';
--step1:创建类型;expect:成功
drop TYPE if exists my_text_type;
call gms_utility.exec_ddl_statement('CREATE TYPE public.my_text_type AS (a text);');
--step2:创建转换函数;expect:成功
call gms_utility.exec_ddl_statement('CREATE OR REPLACE FUNCTION my_text_to_int(txt my_text_type)
RETURNS integer AS $$
BEGIN
    RETURN txt.text::integer;
END;
$$ LANGUAGE plpgsql;');
--step3:创建类型转换;expect:成功
call gms_utility.exec_ddl_statement('CREATE CAST (my_text_type AS integer)
WITH FUNCTION my_text_to_int(my_text_type) AS ASSIGNMENT;');
--step4:使用类型转换;expect:成功
drop table if exists t_exec0018;
create table t_exec0018(a text);
insert into t_exec0018 values(10);
SELECT CAST(a AS integer) FROM t_exec0018;

--step5:删除类型转换;expect:成功
call gms_utility.exec_ddl_statement('drop TYPE public.my_text_type cascade;');
--step6:清理环境;expect:成功
drop extension if exists gms_utility cascade;
reset behavior_compat_options;
